package com.jahentao.patentQuery.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author jahentao
 * @date 2018/5/3
 * @since 1.0
 */
public class ReadExcel {

    /**
     * 总行数
     */
    private Integer totalRows;
    private Integer totalColumns;
    private Workbook wb;
    private InputStream is;
    private Sheet sheet;

    public ReadExcel(Integer totalColumns) {
        this.totalColumns = totalColumns;
    }

    public Integer getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(Integer totalRows) {
        this.totalRows = totalRows;
    }

    /**
     * 读EXCEL文件，获取集合
     * @param filePath
     * @return
     */
    public void getExcelWorkbook(String filePath) {
        //初始化输入流
        InputStream is = null;
        try{
            //根据文件名判断文件是2003版本还是2007版本
            boolean isExcel2003 = true;
            if(filePath.matches("^.+\\.(?i)(xlsx)$")){
                isExcel2003 = false;
            }
            //根据新建的文件实例化输入流
            is = new FileInputStream(filePath);
            Workbook wb = null;
            //根据excel里面的内容读取客户信息
            if(isExcel2003){//当excel是2003时
                wb = new HSSFWorkbook(is);
            }
            else{//当excel是2007时
                wb = new XSSFWorkbook(is);
            }

            //得到第一个shell
            sheet=wb.getSheetAt(0);
            //得到Excel的行数
            this.totalRows=sheet.getPhysicalNumberOfRows();

        }catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 记得关闭文件流
      */
    public void closeStream() {
        if(is !=null) {
            try{
                is.close();
            }catch(IOException e){
                is = null;
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取指定行的记录
     * @return
     */
    public List<String> getLine(int i) {
        Row row = sheet.getRow(i);

        if (row == null) return Collections.emptyList();

        List<String> list = new ArrayList<>();
        // 获取一行记录
        for (int c=0; c<totalColumns; c++) {
            Cell cell = row.getCell(c);
            if (cell != null) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // 提高兼容性、鲁棒性
                // 解决两种空格类型错误：ASCII 32、ASCII 160
                list.add(cell.getStringCellValue().replaceAll("[\\s\\u00A0]+", "").trim());
            }
        }

        return list;
    }
}